package table;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import common.Helper;
import database.DatabaseConnection;

/**
 * 处理用户相关的所有数据库操作的类
 */
public class UserHandle {
	Connection conn;
	Statement stmt;
	ResultSet rs;
	
	/**
	 * 初始化连接
	 */
	private void initConnection() {
		try {
			long t1 = System.currentTimeMillis();
			conn = DatabaseConnection.getConnection();
			stmt = conn.createStatement();
			long last = System.currentTimeMillis() - t1;
			System.out.println("建立连接耗时: " + last + "ms");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public UserHandle() {
		System.out.println("####UserHandle创建####");
		initConnection();
	}
	
	@Override
	protected void finalize() throws Throwable {
		try {
			System.out.println("####UserHandle销毁####");
			conn.close();
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		super.finalize();
	}
	
	/**
	 * 登录合法性检测
	 * @param user 存有userid、密码和所属用户组的User对象
	 * @return 负数表示登陆不合法，其中-2表示密码错误，-1表示权限错误，正数1表示登陆合法
	 */
	public int CheckUser(User user) {
		int ret = -2;
		
		String userid = user.getId();
		String passwd = user.getPassword();
		String group = user.getGroup();
		passwd = Helper.toMd5(passwd);
		if(group.equals("guest")) {
			// 如果是访客，直接跳过认证阶段
			return 1;
		}
		
		try{
			if(conn == null) initConnection();
			String sql = "SELECT * FROM tb_user WHERE user_id='" + userid + "'";
			rs = stmt.executeQuery(sql);
			
			while(rs.next()){
				String md5pwd = rs.getString("user_password");
				String ugroup = rs.getString("user_group");
				if(md5pwd.equalsIgnoreCase(passwd)) { // 验证密码正确性
					ret =  -1;
					if(ugroup.equals(group) || ugroup.equals("admin")) { // 验证权限正确性
						ret = 1;
					}
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * 传入带userid的User对象，补全其他信息（除密码外）
	 * @param 补全后的User对象
	 */
	public void updateInfoById(User user) {
		if(user == null) return;
		
		String userid = user.getId();
		
		try {
			if(conn == null) initConnection();
			String sql = 
			"SELECT tu.user_id, tu.user_name, tu.user_teamid, tu.user_group," + 
			" tt.team_name FROM tb_user as tu LEFT JOIN tb_team as tt" +
			" ON tu.user_teamid=tt.team_id WHERE tu.user_id='"+ userid + "'";
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				String userName = rs.getString("user_name");
				int userTeamid = rs.getInt("user_teamid");
				String userGroup = rs.getString("user_group");
				String teamName = rs.getString("team_name");
				
				user.setName(userName);
				user.setTeamid(userTeamid);
				user.setGroup(userGroup);
				user.setTeamName(teamName);
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 通过userid获取相应用户
	 * @param userid
	 * @return 对应User
	 */
	public User getUserById(String userid) {
		User ret = null;
		try {
			if(conn == null) initConnection();
			String sql = 
			"SELECT tu.user_id, tu.user_name, tu.user_teamid, tu.user_group," + 
			" tt.team_name FROM tb_user as tu LEFT JOIN tb_team as tt" +
			" ON tu.user_teamid=tt.team_id WHERE tu.user_id='"+ userid + "'";
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				String userName = rs.getString("user_name");
				int userTeamid = rs.getInt("user_teamid");
				String userGroup = rs.getString("user_group");
				String teamName = rs.getString("team_name");
				User user = new User();
				user.setId(userid);
				user.setName(userName);
				user.setTeamid(userTeamid);
				user.setGroup(userGroup);
				user.setTeamName(teamName);
				ret = user;
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * 获取userid中包含idScreen字符串的所有User对象
	 * @param idScreen
	 * @return
	 */
	public List<User> getUsersWithIdLike(String idScreen){
		ArrayList<User> list = new ArrayList<>();
		try {
			if(conn == null) initConnection();
			String sql = 
			"SELECT tu.user_id, tu.user_name, tu.user_teamid, tu.user_group," + 
			" tt.team_name FROM tb_user as tu LEFT JOIN tb_team as tt" +
			" ON tu.user_teamid=tt.team_id WHERE tu.user_id like '%"+ idScreen + "%'";
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				User user = new User();
				String userid = rs.getString("user_id");
				String userName = rs.getString("user_name");
				int userTeamid = rs.getInt("user_teamid");
				String userGroup = rs.getString("user_group");
				String teamName = rs.getString("team_name");
				user.setId(userid);
				user.setName(userName);
				user.setTeamid(userTeamid);
				user.setGroup(userGroup);
				user.setTeamName(teamName);
				list.add(user);
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	
	
	/**
	 * 获取所有用户信息
	 * @return 储存着所有用户数据的列表
	 */
	public List<User> getAllUsers(){
		ArrayList<User> list = new ArrayList<>();
		try {
			if(conn == null) initConnection();
			String sql = 
					"SELECT tu.user_id, tu.user_name, tu.user_teamid, tu.user_group," + 
					" tt.team_name FROM tb_user as tu LEFT JOIN tb_team as tt" +
					" ON tu.user_teamid=tt.team_id";
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				String userid = rs.getString("user_id");
				String userName = rs.getString("user_name");
				int userTeamid = rs.getInt("user_teamid");
				String userTeamName = rs.getString("team_name");
				String userGroup = rs.getString("user_group");
				User newuser = new User();
				newuser.setId(userid);
				newuser.setName(userName);
				newuser.setTeamid(userTeamid);
				newuser.setTeamName(userTeamName);
				newuser.setGroup(userGroup);
				list.add(newuser);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 通过userid在数据库中删除指定用户
	 * @param userid
	 * @return 删除成功返回true，否则false
	 */
	public boolean deleteUserById(String userid) {
		boolean ret = false;
		try {
			if(conn == null) initConnection();
			String sql = "DELETE FROM tb_user WHERE user_id = '" + userid + "'";
			int result = stmt.executeUpdate(sql);
			if(result != 0)
				ret = true;
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * 将user的数据更新到数据库
	 * @param old_userid 旧userid
	 * @param user 要更新成的user数据
	 * @return 成功返回true，失败返回false
	 */
	public boolean updateToDB(String old_userid ,User user) {
		boolean ret = false;
		String userid = user.getId();
		String username = user.getName();
		int teamid = user.getTeamid();
		String password = user.getPassword();
		if(password != null && !password.isEmpty()) {
			password = Helper.toMd5(password);
		}
		
		try {
			if(conn == null) initConnection();
			String sql = String.format(
					"UPDATE tb_user SET user_id=\"%s\", user_name=\"%s\", " +
					"user_teamid=\"%d\"",
					userid, username, teamid);
			if(password != null && !password.isEmpty()) { // 如果密码不为空则更新密码
				sql += " ,user_password=\"" + password + "\"";
			}
			sql += " WHERE user_id=\"" + old_userid + "\";";
			System.out.println(sql);
			int result = stmt.executeUpdate(sql);
			if(result != 0)
				ret = true;
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * 批量添加新用户进入数据库
	 * @param list 新用户列表
	 * @return 成功添加的数量
	 */
	public int addUsers(ArrayList<User> list, String mode) {
		int result = 0;
		
		if(list.size() == 0)
			return 0;
		
		try {
			if(conn == null) initConnection();
			if("safe".equalsIgnoreCase(mode)) {
				// 安全模式
				StringBuffer sb = new StringBuffer("INSERT tb_user VALUES ");
				for(User u: list) {
					String userid = u.getId();
					String name = u.getName();
					String password = Helper.toMd5(u.getPassword());
					String teamid = u.getTeamid() + "";
					sb.append(String.format("('%s','%s','%s',%s,'user'),", userid, name, password, teamid));
				}
				sb.deleteCharAt(sb.length()-1); // 将最后的逗号删除
				String sql = sb.toString();
				result = stmt.executeUpdate(sql);	
					
			} else if("normal".equalsIgnoreCase(mode)) {
				// 一般模式
				for(User u: list) {
					result += addUser(u);
				}
			} else if("force".equalsIgnoreCase(mode)) {
				// 强制模式
				for(User u: list) {
					if(addUser(u) != 1) {
						if(updateToDB(u.getId(), u))
							result++;
					}
				}
			}
		
		}catch(Exception e) {
			e.printStackTrace();
		}	
		
		return result;
	}
	
	/**
	 * 添加用户
	 * @param u 用户信息
	 * @return 更新信息条数
	 */
	private int addUser(User u) {
		int result = 0;
		try {
			if(conn == null) initConnection();
			String userid = u.getId();
			String name = u.getName();
			String password = Helper.toMd5(u.getPassword());
			String teamid = u.getTeamid() + "";
			String sql = String.format("INSERT tb_user VALUES ('%s','%s','%s',%s,'user');", userid, name, password, teamid);
			result = stmt.executeUpdate(sql);	
		}catch(Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	/**
	 * 设置用户权限
	 * @param userid 用户id
	 * @param group 修改成的权限组可以时“admin”或“user”
	 * @return
	 */
	public boolean changeGroup(String userid, String group) {
		if(!"admin".equals(group) && !"user".equals(group)) return false;
		boolean result = false;
		try {
			if(conn == null) initConnection();
			String sql = String.format("UPDATE tb_user SET user_group='%s' WHERE user_id='%s';",group, userid);
			if(stmt.executeUpdate(sql) == 1) result = true;	
		}catch(Exception e) {
			e.printStackTrace();
		}
		return result;
	}
}
